package com.hualude.roadsign.repository;

import com.hualude.roadsign.dto.DTRequestDto;
import com.hualude.roadsign.dto.RoadpoleDto;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.Transformers;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;

/**
 * Created by shao-yikun on 2017/4/7.
 */
public class RoadPoleRepositoryImpl {

    @PersistenceContext
    private EntityManager em;

    public List<RoadpoleDto> getRoadPoleCollect4DT(DTRequestDto dtRequestDto, Integer orgId, String actorIds,
                                                   String startRoad, String endRoad, String roadName, String saveState) throws Exception {
        String sql = "select rp.*, ur.userName, og.orgName ,og2.orgName as mainOrgName from tb_roadpole rp, tb_user ur, tb_org og,tb_org og2 " +
                "where rp.orgId = og.orgId and rp.mainOrgId=og2.orgId and rp.CreateUserId = ur.UserId " +
                "and rp.startRoad like '%" + startRoad + "%' and rp.endRoad like '%" + endRoad + "%' " +
                "and rp.roadName like '%" + roadName + "%' and rp.roadPoleSecurity like '%" + saveState + "%' and  rp.projectNumber like '%" + dtRequestDto.getSearch() + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and rp.mainOrgId=" + orgId + " ";
        else
            sql += "and rp.orgId = " + orgId + " ";
        if (startRoad != "" || endRoad != "") {
            sql += " union select r.*, u.userName, o.orgName ,o2.orgName as mainOrgName from tb_roadpole r, tb_user u, tb_org o ,tb_org o2 " +
                    "where r.orgId = o.orgId and r.mainOrgId=o2.orgId and r.CreateUserId = u.UserId " +
                    "and r.endRoad like '%" + startRoad + "%' and r.startRoad like '%" + endRoad + "%' " +
                    "and r.roadName like '%" + roadName + "%' and r.roadPoleSecurity like '%" + saveState + "%' and  r.projectNumber like '%" + dtRequestDto.getSearch() + "%' ";
            if (actorIds.indexOf('1') >= 0)
                sql += "";
            else if (actorIds.indexOf('7') >= 0)
                sql += "and r.mainOrgId=" + orgId + " ";
            else
                sql += "and r.orgId = " + orgId + " ";
        }
        sql += "order by roadPoleCreateTime desc";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(RoadpoleDto.class));
        return query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
    }

    public Integer getCount4DT(Integer orgId, String actorIds, String startRoad,
                               String endRoad, String roadName, String saveState, String projectNumber) throws Exception {
        String sql = "select count(*) from tb_roadpole rp, tb_user ur, tb_org og " +
                "where rp.orgId = og.orgId and rp.CreateUserId = ur.UserId " +
                "and rp.startRoad like '%" + startRoad + "%' and rp.endRoad like '%" + endRoad + "%' " +
                "and rp.roadName like '%" + roadName + "%' and rp.roadPoleSecurity like '%" + saveState + "%' and  rp.projectNumber like '%" + projectNumber + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and rp.mainOrgId=" + orgId + " ";
        else
            sql += "and rp.orgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        Integer count = Integer.parseInt(query.getSingleResult().toString());
        if (startRoad != "" || endRoad != "") {
            String sql2 = "select count(*) from tb_roadpole r, tb_user u, tb_org o " +
                    "where r.orgId = o.orgId and r.CreateUserId = u.UserId and r.endRoad like '%" + startRoad + "%' and r.startRoad like '%" + endRoad + "%' " +
                    "and r.roadName like '%" + roadName + "%' and r.roadPoleSecurity like '%" + saveState + "%' and r.projectNumber like '%" + projectNumber + "%' ";
            if (actorIds.indexOf('1') >= 0)
                sql2 += "";
            else if (actorIds.indexOf('7') >= 0)
                sql2 += "and r.mainOrgId=" + orgId + " ";
            else
                sql2 += "and r.orgId = " + orgId + " ";
            query = em.createNativeQuery(sql2);
            count += Integer.parseInt(query.getSingleResult().toString());
        }
        return count;
    }


    public List<RoadpoleDto> getRoadPoleAudit4DT(DTRequestDto dtRequestDto, Integer orgId, String actorIds) throws Exception {
        String sql = "select rp.*, ur.userName, og.orgName  " +
                "from tb_roadpole rp, tb_user ur, tb_org og " +
                "where rp.orgId = og.orgId and rp.CreateUserId = ur.UserId AND rp.roadPoleIsComplete=0 " +
                "and concat(rp.roadName, rp.startRoad, rp.endRoad) " +
                "like '%" + dtRequestDto.getSearch() + "%' ";
        if (actorIds.indexOf('1') >= 0) {
            sql += "";
        } else {
            sql += "and rp.orgId = " + orgId + " ";
        }
        sql += " order by rp.roadPoleIsComplete ASC,rp.roadPoleCreateTime desc ";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(RoadpoleDto.class));
        return query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
    }

    public Integer getCount4RoadPoleAuditDT(String search, Integer orgId, String actorIds) throws Exception {
        String sql = "select count(*) " +
                "from tb_roadpole rp, tb_user ur, tb_org og " +
                "where rp.orgId = og.orgId and rp.CreateUserId = ur.UserId AND rp.roadPoleIsComplete=0 " +
                "and concat(rp.roadName, rp.startRoad, rp.endRoad) " +
                "like '%" + search + "%' ";
        if (actorIds.indexOf('1') >= 0) {
            sql += "";
        } else {
            sql += "and rp.orgId = " + orgId + " ";
        }
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public List<RoadpoleDto> getCoordinateByOrgId(Integer orgId,
                                                  String actorIds,
                                                  double neLongitude,
                                                  double neLatitude,
                                                  double swLongitude,
                                                  double swLatitude) throws Exception {
        String sql = "select * from tb_roadpole ";
        sql += "where longitude < :neLongitude and longitude > :swLongitude " +
                "and latitude < :neLatitude and latitude > :swLatitude ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('7') >= 0)
            sql += "and mainOrgId = " + orgId;
        else
            sql += "and orgId = " + orgId;
        Query query = em.createNativeQuery(sql);
        query.setParameter("neLongitude", String.valueOf(neLongitude));
        query.setParameter("swLongitude", String.valueOf(swLongitude));
        query.setParameter("neLatitude", String.valueOf(neLatitude));
        query.setParameter("swLatitude", String.valueOf(swLatitude));
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(RoadpoleDto.class));
        return query.getResultList();
    }

    public List<RoadpoleDto> getRoadPoleBySearch(Integer orgId, String actorIds, String strSearch, Integer mCount,
                                                 Integer mySize) throws Exception {
        String sql;
        if (!strSearch.equals("")) {
            String[] namesArray = strSearch.split(",");
            if (!namesArray[0].equals("")) {
                sql = "select * from tb_roadpole where roadName like '%" + namesArray[0] + "%' ";
            } else {
                sql = "select * from tb_roadpole where roadName like '%%' ";
            }
            if (namesArray[1].equals("起始") && namesArray[2].equals("结束")) {
                sql += "";
            } else if (!namesArray[1].equals("起始") && namesArray[2].equals("结束")) {
                sql += "and startRoad like '%" + namesArray[1] + "%' ";
            } else {
                sql += "and startRoad like '%" + namesArray[1] + "%' and endRoad like '%" + namesArray[2] + "%' ";
            }
            if (namesArray[3].equals("安全")) {
                sql += "and roadPoleSecurity = 0 ";
            } else if (namesArray[3].equals("不安全")) {
                sql += "and roadPoleSecurity = 1 ";
            }
            if (!namesArray[4].equals("null")) {
                sql += "and city like '%" + namesArray[4] + "%'";
            }
            if (actorIds.indexOf('1') >= 0) {
                sql += "";
            } else if (actorIds.indexOf('7') >= 0) {
                sql += "and mainOrgId = " + orgId + " ";
            } else {
                sql += "and orgId = " + orgId + " ";
            }
        } else {
            sql = "select * from tb_roadpole ";
            if (actorIds.indexOf('1') >= 0) {
                sql += "";
            } else if (actorIds.indexOf('7') >= 0) {
                sql += "where mainOrgId = " + orgId + " ";
            } else {
                sql += "where orgId = " + orgId + " ";
            }
        }
        sql += "order by roadPoleCreateTime desc";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(RoadpoleDto.class));
        return query.setFirstResult((mCount - 1) * mySize).setMaxResults(mySize).getResultList();
    }

    public List<RoadpoleDto> getRoadPoleCoordinate4Map(Integer orgId, Integer orgType, Integer actorId) throws Exception {
        String sql = "select roadPoleId, longitude,latitude from tb_roadpole ";
        if (actorId != 1) {
            if (orgType == 0)
                sql += "where orgId=" + orgId + "";
            if (orgType == 1)
                sql += "where mainOrgId=" + orgId + "";
        }
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(RoadpoleDto.class));
        return query.getResultList();
    }

    public List<RoadpoleDto> getRoadPoleByXY(String longitude, String latitude) throws Exception {
        String sql = "select rp.* ,ur.userName, og.orgName,og2.orgName as mainOrgName " +
                "from tb_roadpole rp, tb_user ur, tb_org og ,tb_org og2 " +
                "where rp.longitude = " + longitude + " and rp.latitude = " + latitude + " and rp.orgId = og.orgId " +
                "and rp.mainOrgId=og2.orgId and rp.CreateUserId = ur.UserId";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(RoadpoleDto.class));
        return query.getResultList();

    }

    public List<String> getRoadByRoadName(String roadName, Integer actorId, Integer orgId) throws Exception {
        String sql = "select r.startRoad as startRoad from tb_roadpole r where r.RoadName like '%" + roadName + "%' ";
        if (actorId == 1)
            sql += "";
        else if (actorId == 7)
            sql += " and r.mainOrgId=" + orgId + " ";
        else
            sql += " and r.orgId=" + orgId + " ";
        sql += " UNION select rp.EndRoad as startRoad from tb_roadpole rp where  rp.RoadName like '%" + roadName + "%' ";
        if (actorId == 1)
            sql += "";
        else if (actorId == 7)
            sql += " and rp.mainOrgId=" + orgId + " ";
        else
            sql += " and rp.orgId=" + orgId + " ";
        Query query = em.createNativeQuery(sql);
        return query.getResultList();
    }

    public List<String> getSectionByRoadName(String roadName, Integer actorId, Integer orgId) throws Exception {
        String sql = "select r.startRoad as startRoad from tb_roadpole r where r.EndRoad like '%" + roadName + "%' ";
        if (actorId == 1)
            sql += "";
        else if (actorId == 7)
            sql += "and r.mainOrgId=" + orgId + " ";
        else
            sql += "and r.orgId=" + orgId + " ";
        sql += " UNION select rp.EndRoad as startRoad from tb_roadpole rp where  rp.startRoad like '%" + roadName + "%' ";
        if (actorId == 1)
            sql += "";
        else if (actorId == 7)
            sql += "and rp.mainOrgId=" + orgId + " ";
        else
            sql += "and rp.orgId=" + orgId + " ";
        Query query = em.createNativeQuery(sql);
        return query.getResultList();
    }

    public List<RoadpoleDto> getRoadPole2Report4DT(DTRequestDto dtRequestDto, Integer orgId, Integer actorId, String startRoad, String endRoad) throws Exception {
        String sql = "select rp.*, ur.userName, og.orgName from tb_roadpole rp, tb_user ur, tb_org og " +
                "where rp.orgId = og.orgId and rp.CreateUserId = ur.UserId and rp.startRoad like '%" + startRoad + "%' and rp.endRoad like '%" + endRoad + "%' " +
                "and concat(rp.roadName, rp.startRoad, rp.endRoad) " +
                "like '%" + dtRequestDto.getSearch() + "%' ";
        if (actorId == 1) {
            sql += "";
        } else {
            sql += "and rp.orgId = " + orgId + " ";
        }
        if (startRoad != "" || endRoad != "") {
            sql += " union select r.*, u.userName, o.orgName from tb_roadpole r, tb_user u, tb_org o " +
                    "where r.orgId = o.orgId and r.CreateUserId = u.UserId and r.endRoad like '%" + startRoad + "%' and r.startRoad like '%" + endRoad + "%' " +
                    "and concat(r.roadName, r.startRoad, r.endRoad) " +
                    "like '%" + dtRequestDto.getSearch() + "%' ";
            if (actorId == 1) {
                sql += "";
            } else {
                sql += "and r.orgId = " + orgId + " ";
            }
        }
        sql += "order by roadPoleCreateTime desc";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(RoadpoleDto.class));
        return query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
    }

    public Integer getRoadPole2ReportCount4DT(String strSearch, Integer orgId, Integer actorId) throws Exception {
        String sql = "select count(*) " +
                "from tb_roadpole rp, tb_user ur, tb_org og " +
                "where rp.orgId = og.orgId and rp.CreateUserId = ur.UserId " +
                "and concat(rp.roadName, rp.startRoad, rp.endRoad) " +
                "like '%" + strSearch + "%' ";
        if (actorId == 1) {
            sql += "";
        } else {
            sql += "and rp.orgId = " + orgId + " ";
        }
        sql += "order by rp.roadPoleCreateTime desc";
        Query query = em.createNativeQuery(sql);
        return Integer.parseInt(query.getSingleResult().toString());
    }

    public List<RoadpoleDto> getRoadPoleByFiltrate(String roadName, String city, Integer orgId, String actorIds) throws Exception {
        String sql;
        sql = "select * from tb_roadpole ";
        if (roadName.equals("全部") || roadName.equals("")) {
            if (actorIds.indexOf('1') >= 0) {
                sql += "";
            } else if (actorIds.indexOf('7') >= 0) {
                sql += "where mainOrgId = " + orgId + " ";
            } else {
                sql += "where orgId = " + orgId + " ";
            }
        } else {
            sql += "where roadName like '%" + roadName + "%' ";
            if (actorIds.indexOf('1') >= 0) {
                sql += "";
            } else if (actorIds.indexOf('7') >= 0) {
                sql += "and mainOrgId = " + orgId + " ";
            } else {
                sql += "and orgId = " + orgId + " ";
            }
        }
        if (city.equals("全部") || city.equals("")) {
            sql += "";
        } else {
            sql += "and city like '%" + city + "%' ";
        }
        sql += "order by roadPoleCreateTime desc";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(RoadpoleDto.class));
        return query.getResultList();
    }

    public List<RoadpoleDto> getRoadPole4ReportDT(DTRequestDto dtRequestDto, Integer orgId, String actorIds,
                                                  String startRoad, String endRoad, String roadName, String isSave) throws Exception {
        String sql = "select rp.*, ur.userName, og.orgName from tb_roadpole rp, tb_user ur, tb_org og " +
                "where rp.orgId = og.orgId and rp.CreateUserId = ur.UserId " +
                "and rp.startRoad like '%" + startRoad + "%' and rp.endRoad like '%" + endRoad + "%' " +
                "and rp.roadName like '%" + roadName + "%' and rp.roadPoleSecurity like '%" + isSave + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('6') >= 0)
            sql += "and rp.orgId = " + orgId + " ";
        if (startRoad != "" || endRoad != "") {
            sql += " union select r.*, u.userName, o.orgName from tb_roadpole r, tb_user u, tb_org o " +
                    "where r.orgId = o.orgId and r.CreateUserId = u.UserId " +
                    "and r.endRoad like '%" + startRoad + "%' and r.startRoad like '%" + endRoad + "%' " +
                    "and r.roadName like '%" + roadName + "%' and r.roadPoleSecurity like '%" + isSave + "%' ";
            if (actorIds.indexOf('1') >= 0)
                sql += "";
            else if (actorIds.indexOf('6') >= 0)
                sql += "and r.orgId = " + orgId + " ";
        }
        sql += "order by roadPoleCreateTime desc";
        Query query = em.createNativeQuery(sql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(RoadpoleDto.class));
        return query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
    }

    public Integer getRoadPole4ReportCountDT(Integer orgId, String actorIds, String startRoad, String endRoad, String roadName, String isSave) throws Exception {
        String sql = "select count(*) from tb_roadpole rp, tb_user ur, tb_org og " +
                "where rp.orgId = og.orgId and rp.CreateUserId = ur.UserId " +
                "and rp.startRoad like '%" + startRoad + "%' and rp.endRoad like '%" + endRoad + "%' " +
                "and rp.roadName like '%" + roadName + "%' and rp.roadPoleSecurity like '%" + isSave + "%' ";
        if (actorIds.indexOf('1') >= 0)
            sql += "";
        else if (actorIds.indexOf('6') >= 0)
            sql += "and rp.orgId = " + orgId + " ";
        Query query = em.createNativeQuery(sql);
        Integer count = Integer.parseInt(query.getSingleResult().toString());
        if (startRoad != "" || endRoad != "") {
            String sql2 = "select count(*) from tb_roadpole r, tb_user u, tb_org o " +
                    "where r.orgId = o.orgId and r.CreateUserId = u.UserId and r.endRoad like '%" + startRoad + "%' and r.startRoad like '%" + endRoad + "%' " +
                    "and r.roadName like '%" + roadName + "%' and r.roadPoleSecurity like '%" + isSave + "%' ";
            if (actorIds.indexOf('1') >= 0)
                sql2 += "";
            else if (actorIds.indexOf('6') >= 0)
                sql2 += "and r.orgId = " + orgId + " ";
            query = em.createNativeQuery(sql2);
            count += Integer.parseInt(query.getSingleResult().toString());
        }
        return count;
    }
}
